package com.xxxt.utils;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.xxxt.entity.ClassGrade;
import com.xxxt.entity.Student;
import com.xxxt.entity.Teacher;

/**
 * 
 * @author huangrusiehng
 *
 */
public class  ImportExcelUtil {


	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
 

public static List<Student> ImportExcelWithStudent(File file,String passwordWithKey,String tno,String classGradeId) throws IOException{
	List<Student> list = new ArrayList<>();
	Student stu;
	FileInputStream fis = null;
	try {
		fis = new FileInputStream(file);
	} catch (FileNotFoundException e) {
		e.printStackTrace();
	}
	Workbook work = null;
	String fileType = file.getName().substring(file.getName().lastIndexOf("."));
	try{
		if (excel2003L.equals(fileType)) {
			work = new HSSFWorkbook(fis); // 2003-
		} else if (excel2007U.equals(fileType)) {
			work = new XSSFWorkbook(fis); // 2007+
		} else {
			// "解析的文件格式有误！"
			return null;
		}
		Sheet sheet = null;
		// 因为Excel不一定只有一张sheet，遍历Excel中所有的sheet
		for (int i = 0; i < work.getNumberOfSheets(); i++) {
			sheet = work.getSheetAt(i);
			if (sheet == null)
				continue;
			//遍历当前sheet的每一行
			Row row ;
			for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
				row = sheet.getRow(j);
				// 遍历所有的列
				stu = new Student();
				for (int y = row.getFirstCellNum(); y < row.getLastCellNum(); y++) {
					if(y==0&&row.getCell(y)!=null) stu.setSno((String) getCellValue(row.getCell(y)));//设置学生学号
					if(y==1&&row.getCell(y)!=null) stu.setName((String) getCellValue(row.getCell(y)));//设置学生名字
					stu.setPassword(passwordWithKey);//设置密码
					if(y==2&&row.getCell(y)!=null) stu.setPhone((String) getCellValue(row.getCell(y)));//设置学生手机号
					if(y==3&&row.getCell(y)!=null) stu.setEmail((String) getCellValue(row.getCell(y)));//设置学生邮箱
					if(y==4&&row.getCell(y)!=null) stu.setIdcard((String) getCellValue(row.getCell(y)));
					if(y==5&&row.getCell(y)!=null) stu.setLiveSituation((String) getCellValue(row.getCell(y)));
					if(y==6&&row.getCell(y)!=null) stu.setBirthPlace((String) getCellValue(row.getCell(y)));
					stu.setTeacher(new Teacher(tno));//设置学生的老师属性
					stu.setClassGrade(new ClassGrade(classGradeId));//设置学生班级属性
				}
				
				if(!"".equals(stu.getSno()))
				list.add(stu);
			}
		}
		work.close();
	}catch(Exception e){
		e.printStackTrace();
	}
	finally {
		file.delete();
	}
	return list;
}

/**
 * 描述：对表格中数值进行格式化
 *
 * @param cell
 * @return
 */
public static Object getCellValue(Cell cell) {
	Object value = null;
	DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
	SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
	DecimalFormat df2 = new DecimalFormat("0"); // 格式化数字

	switch (cell.getCellTypeEnum()) {
		case STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case NUMERIC:
			if ("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case BLANK:
			value = "";
			break;
		default:
			break;
	}
	return value;
}
/**
 *  所有对象的excel导入
 * @param in
 * @param fileName
 * @param map
 * @return
 */
	public static List<Map<String,Object>> ImportExcel(InputStream in,String fileName,Map<String, String> map) {
		
		InputStream inStr=null;
		List<Map<String, Object>> ls = new ArrayList<Map<String, Object>>();
		try {
			inStr = new FileInputStream(new File(fileName));
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		Workbook wb=null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		try {					
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		// 返回数据
		
		Sheet sheet = null;//页
		Row row = null;//行
		Cell cell = null;//列
		for(int i=0;i<wb.getNumberOfSheets();i++){//判断有多少页
			 sheet = wb.getSheetAt(i);//获取页数
			if (sheet==null) { 
				continue;
			}
			//取第一行为标题
			row=sheet.getRow(0);
			String strings[]=null;
			if (row!=null) {
				strings=new String[row.getLastCellNum()];
				for(int a=row.getFirstCellNum();a<row.getLastCellNum();a++){
					cell= row.getCell(a);
					strings[a] =(String) getCellValue(cell);
				}
			}else {
				continue;
			}
			//遍历sheet所有的行
			for (int j = 1; j < sheet.getLastRowNum() + 1; j++) {
				row = sheet.getRow(j);
				if (isRowEmpty(row)) {
					continue;
				}
				Map<String,Object> mapper = new HashMap<String,Object>();
				//遍历所有的列
				for(int a=row.getFirstCellNum();a<row.getLastCellNum();a++){
					cell=row.getCell(a);
					String key=strings[a];
					mapper.put(map.get(key), getCellValue(cell));
				}
				ls.add(mapper);
			}					
		}
		wb.close();
		} 
		catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return ls;		
	}
	/**
	 * 过滤空行
	 * @param row
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public static boolean isRowEmpty(Row row) {
		for (int c = row.getFirstCellNum(); c < row.getLastCellNum(); c++) {
			Cell cell = row.getCell(c);
			if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK)
				return false;
		}
		return true;
	}
}
